
import sys
import os

import MySQLdb
from SimpleXMLRPCServer import SimpleXMLRPCServer


home_dir = os.path.expanduser("~")
conference_dir = os.path.join(home_dir,".qlive/conference")

def getAuth():
	
	file = open(os.path.join(conference_dir,"Auth_DataBase.qlive") ,"r")
	data = file.readline().split(" ") 
	file.close()
	host = data[0].strip()
	user = data[1].strip()
	pwd = data[2].strip()
	db = data[3].strip()

	'''	
	host = "192.168.0.2"
	user = "root"
	pwd = "admin1234"
	db = "ko_xbmc2"
	'''
	
	return host, user, pwd, db
	
def getCursor():
	#host,user,pwd = "localhost", "root", "admin1234" #getAuth()
	host,user,pwd,db = getAuth()
	connection = MySQLdb.connect(host=host, user=user, passwd=pwd, db=db)
	connection.set_character_set('utf8')

	
	cursor = connection.cursor()
	cursor.execute("SET NAMES utf8;")
	cursor.execute("SET CHARACTER SET utf8;")
	cursor.execute("SET character_set_connection=utf8;")
	return cursor

'''
def updateConference(room):
	sql = "update conference set attendant = attendant + 1 where attendant < valence and room='%s'" % (room)
	cursor = getCursor()
	result = cursor.execute(sql)
	cursor.execute('commit')
	
	if result == 0 :
		return False
	else :
		return True
def decreaseAttendant(room):
	sql = "update conference set attendant = attendant - 1 where attendant > 0 and room='%s'" % (room)
	cursor = getCursor()
	result = cursor.execute(sql)
	cursor.execute('commit')
	
	if result == 0 :
		return False
	else :
		return True
		
def getConferenceData():
	cursor = getCursor()
	cursor.execute("select * from conference")
	result = cursor.fetchall()
	cursor.close()
	print len(result)
	print result
	return result
'''
	
def decreaseAttendant2(appId) :
	cursor = getCursor()
	#sql = "update appointments set chk_status = 0 where pass = '%s'" % (password)
	sql = "update appointments set countnumber = countnumber -1 where countnumber > 0"
	
	print sql
	
	result = cursor.execute(sql)
	cursor.execute('commit')
	cursor.close()
	
	if result == 0 :
		return False
	else :
		return True

def updateConference2(appId):
	cursor = getCursor()
	#sql = "update appointments_detail set chk_status = 1 where pass ='%s'" % (password)
	sql = "select numberOfRoom from room,appointments where room.room_id = appointments.room_id and appointments_id = %d" % (appId)
	
	print sql
	
	cursor.execute(sql)
	valence = cursor.fetchall()[0][0]
	
	print 'valence >> %d\n' % (valence)
	
	sql = "update appointments set countnumber = countnumber + 1 where countnumber < %d and appointments_id = %d" % (valence, int(appId))
	result = cursor.execute(sql)
	cursor.execute('commit')
	
	print sql
	
	if result == 0 :
		return False
	else :
		return True
	
def getConferenceData2():
	cursor = getCursor()

	import datetime
	today = datetime.datetime.now()
	today = str(today)[0:10]
	cursor.execute(
		'''select appointments.appointments_id, room.room_name ,numberofroom,
		appointments_name, appointments_starttime, appointments_endtime, url , CountNumber
		from appointments,room where appointments.room_id = room.room_id and appointments_starttime like \'''' + today + "%'"
		)
	results = cursor.fetchall()
	
	# close cursor
	cursor.close()
	
	datas = []
	
	cursor = getCursor()
	
	for result in results :
		data = {}
		data["APP_ID"] = result[0]
		data["ROOM"] = result[1]
		data["VALENCE"] = result[2]
		data["DESCRIPTION"] = result[3]
		data["START"] = result[4]
		data["END"] = result[5]
		
		data["URL"] = result[6]
		
		# get passwords
		cursor = getCursor()
		cursor.execute('select pass from appointments_detail where appointments_id = %d' % (data["APP_ID"] ))
		
		passes = cursor.fetchall()
		data['PASSWORD'] = passes
		cursor.close()
	
		
		data["ATTENDANT"] = result[7]
		
		if not data["ATTENDANT"] :
			data["ATTENDANT"] = 0
		
		print data , "\n"
		
		datas.append(data)
	
	cursor.close()
	print datas
	return datas
		
	

if __name__ == "__main__" :
	server = SimpleXMLRPCServer(('localhost',9001), allow_none=True)
	server.register_function(updateConference2)
	server.register_function(getConferenceData2)
	server.register_function(decreaseAttendant2)
	print "server running @ localhost 9001 ..."
	server.serve_forever()
	
	
# test query 
'''
select appointments.appointments_id, appointments.room_id ,numberofroom, title, appointments_starttime, appointments_endtime,title from appointments,room where appointments.room_id = room.room_id;
'''
